﻿using System.Data.SQLite;

namespace QQTools.Data
{
    public class DatabaseContext
    {
        // private static readonly string connectionString = "Data Source=qqhelper.db;Version=3;DefaultTimeout=10;";
        // private static readonly string connectionString = "Data Source=C:\\QQ怀旧助手配置\\qqtools.db;Version=3;DefaultTimeout=10;"; // Version=3;
        private static readonly string connectionString = "Data Source=C:\\QQ怀旧助手配置\\qqhelper.db;Version=3;DefaultTimeout=10;"; // Version=3;
        public static string ConnectionString => connectionString;

        public static void InitializeDatabase()
        {

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string createQQTable = @"
                    CREATE TABLE IF NOT EXISTS qq (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT NOT NULL UNIQUE,
                        nickname TEXT,
                        title TEXT,
                        last_handle INTEGER,
                        last_pid INTEGER,
                        group_number TEXT,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";

                string createGroupTable = @"
                    CREATE TABLE IF NOT EXISTS groups (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        group_number TEXT,
                        qq TEXT,
                        group_name TEXT,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (qq) REFERENCES qq(qq)
                    );";

                string createAddGroupTable = @"
                    CREATE TABLE IF NOT EXISTS add_group (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT,
                        group_number TEXT,
                        status INTEGER DEFAULT 1,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                       
                    );";

                string createInviteGroupTable = @"
                    CREATE TABLE IF NOT EXISTS invite_group (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT,
                        group_number TEXT,
                        invited_list_id INTEGER,
                        status INTEGER DEFAULT 1,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (invited_list_id) REFERENCES invited_list(id)
                    );";

                string createInvitedListTable = @"
                    CREATE TABLE IF NOT EXISTS invited_list (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT,
                        invite_number INTEGER,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";

                string createInviteQQTable = @"
                    CREATE TABLE IF NOT EXISTS invite_qq (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT,
                        invite_number INTEGER DEFAULT 0,
                        invite_success_count INTEGER DEFAULT 0,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";

                string createAddGroupQQTable = @"
                   CREATE TABLE IF NOT EXISTS add_group_qq (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT NOT NULL,
                        current_batch INTEGER NOT NULL DEFAULT 1,
                        current_batch_add_count INTEGER NOT NULL DEFAULT 0,
                        total_add INTEGER NOT NULL DEFAULT 0,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";

                string createBlockGroupTable = @"
                   CREATE TABLE IF NOT EXISTS block_groups (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT NOT NULL,
                        group_number TEXT,
                        status INTEGER DEFAULT 1,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";

                string createBlockGroupQQTable = @"
                   CREATE TABLE IF NOT EXISTS block_group_qq (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT NOT NULL,
                        block_number INTEGER DEFAULT 0,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";

                string createForwardGroupMsgQQTable = @"
                   CREATE TABLE IF NOT EXISTS forward_group_msg_qq (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT NOT NULL,
                        forward_count INTEGER DEFAULT 0,
                        failed_count INTEGER DEFAULT 0,
                        resource_index INTEGER DEFAULT 1,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";

                string createForwardGroupMsgTable = @"
                   CREATE TABLE IF NOT EXISTS forward_group_msg (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT NOT NULL,
                        group_number TEXT,
                        message_content TEXT,
                        message_type INTEGER DEFAULT 0,
                        status INTEGER DEFAULT 0,
                        forward_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        retry_count INTEGER DEFAULT 0,
                        forward_count INTEGER DEFAULT 0,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";


                string createSendGroupMsgQQTable = @"
                   CREATE TABLE IF NOT EXISTS send_group_msg_qq (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT NOT NULL,
                        send_count INTEGER DEFAULT 0,
                        failed_count INTEGER DEFAULT 0,
                        resource_index INTEGER DEFAULT 1,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";

                string createSendGroupMsgTable = @"
                   CREATE TABLE IF NOT EXISTS send_group_msg (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        qq TEXT NOT NULL,
                        group_number TEXT,
                        message_content TEXT,
                        message_type INTEGER DEFAULT 0,
                        status INTEGER DEFAULT 0,
                        send_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        retry_count INTEGER DEFAULT 0,
                        send_count INTEGER DEFAULT 0,
                        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
                    );";



                using (var command = new SQLiteCommand(createQQTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createGroupTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createAddGroupTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createInviteGroupTable, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createInvitedListTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createInviteQQTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createAddGroupQQTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createBlockGroupTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createBlockGroupQQTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createForwardGroupMsgQQTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createForwardGroupMsgTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createSendGroupMsgQQTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createSendGroupMsgTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                
                // 检查 invite_group 表是否已经包含 group_number 字段
                using (var command = new SQLiteCommand(@"
                    PRAGMA table_info(invite_group);
                ", connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        bool hasGroupNumber = false;
                        while (reader.Read())
                        {
                            if (reader["name"].ToString() == "group_number")
                            {
                                hasGroupNumber = true;
                                break;
                            }
                        }

                        if (!hasGroupNumber)
                        {
                            // 添加 group_number 字段
                            using (var alterCommand = new SQLiteCommand(@"
                                ALTER TABLE invite_group ADD COLUMN group_number TEXT;
                            ", connection))
                            {
                                alterCommand.ExecuteNonQuery();
                            }
                        }
                    }
                }
                // invite_success_count
                using (var command = new SQLiteCommand(@"
                    PRAGMA table_info(invite_qq);
                ", connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        bool hasInviteSuccessCount = false;
                        while (reader.Read())
                        {
                            if (reader["name"].ToString() == "invite_success_count")
                            {
                                hasInviteSuccessCount = true;
                                break;
                            }
                        }

                        if (!hasInviteSuccessCount)
                        {
                            // 添加 invite_success_count 字段
                            using (var alterCommand = new SQLiteCommand(@"
                                ALTER TABLE invite_qq ADD COLUMN invite_success_count INTEGER;
                            ", connection))
                            {
                                alterCommand.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }
    }
}
